﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LTSMZ.life.Model;
using System.Data;
using Qianli.DirSir.Common;
using System.Data.SqlClient;

namespace LTSMZ.life.DAL
{
    public class StockDal
    {

        Qianli.DirSir.Common.SQLHelper sql = new Qianli.DirSir.Common.SQLHelper();

        /// <summary>
        /// 获取库存信息
        /// </summary>
        /// <returns></returns>
        public List<Stock> StockShow()
        {
            string str = "select Stock.StockID,Product.Pname,Stock.SpotStocks,Stock.AvailableStocks,Stock.StockState,Stock.Sum from Stock join Product on Stock.Pid=Product.Pid";
            DataTable table = sql.ExecDataTable(str);
            List<Stock> st = new List<Stock>();
            foreach (DataRow i in table.Rows)
            {
                Stock s = new Stock();
                s.StockID = Convert.ToInt32(i["StockID"]);
                s.Pname = i["Pname"].ToString();
                s.SpotStocks = Convert.ToInt32(i["SpotStocks"]);
                s.AvailableStocks = Convert.ToInt32(i["AvailableStocks"]);
                if(Convert.ToInt32( s.AvailableStocks)<100)
                {
                    s.StockMS = "库存不足，请及时补充货源";
                }
                else
                {
                    s.StockMS = "库存充足，无需担心";
                }
                s.StockState = Convert.ToInt32(i["StockState"]);
                s.Sum = Convert.ToInt32(i["Sum"]);
                st.Add(s);
            }
            return st;
        }

        Stock s = new Stock();
        /// 下拉框显示
        /// </summary>
        /// <returns></returns>
        public List<Product> Selectpstorck()
        {
            SqlConnection con = new SqlConnection("Data Source=169.254.236.246;Initial Catalog=lift;User ID=sa;pwd=123123");
            string sql = "select Product.Pid, Product.Pname from Product  where Product.Pname not in (select Product.Pname from Product join Stock on Stock.Pid=Product.Pid)";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            adapter.Fill(table);

            List<Product> list = new List<Product>();
            foreach (DataRow row in table.Rows)
            {
                Product pro = new Product();
                pro.Pid = Convert.ToInt32(row["Pid"]);
                pro.Pname = row["Pname"].ToString();

                list.Add(pro);
            }

            return list;
        }
        /// <summary>
        /// 进货
        /// </summary>
        /// <param name="Jin"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int JinStock(int Jin,int id)
        {
            string str = 
                "update Stock set SpotStocks=(SpotStocks+@num),AvailableStocks=(AvailableStocks+@num) where StockID=@id";
            SqlParameter[] par ={
                               new SqlParameter("@num",Jin),
                               new SqlParameter("@id",id)
                               };
            int i = sql.ExecNonquery(str, par);
            return i;
        }
        /// <summary>
        /// 根据编号获取库存信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Stock Get_Stock(int id)
        {
            string str = "select Stock.StockID,Product.Pname,Stock.SpotStocks,Stock.AvailableStocks,Stock.StockState,Stock.Sum from Stock join Product on Stock.Pid=Product.Pid where StockID=@id";
            SqlParameter[] par ={
                               new SqlParameter("@id",id)
                               };
            SqlDataReader reader =sql.ExecReader(str,par);
            Stock s = new Stock();
            while (reader.Read())
            {
                s.StockID = Convert.ToInt32(reader["StockID"]);
                s.Pname = reader["Pname"].ToString();
                s.SpotStocks = Convert.ToInt32(reader["SpotStocks"]);
                s.AvailableStocks = Convert.ToInt32(reader["AvailableStocks"]);
                if (Convert.ToInt32(s.AvailableStocks) < 100)
                {
                    s.StockMS = "库存不足，请及时补充货源";
                }
                else
                {
                    s.StockMS = "库存充足，无需担心";
                }
                s.Sum=Convert.ToInt32(reader["Sum"]);
            }
            return s;
        }
        /// <summary>
        /// 添加库存信息
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int AddStock(Stock s)
        {


            string str = string.Format
                ("insert  into Stock(SpotStocks,AvailableStocks,StockState,Sum,Pid) values(@SpotStocks,@AvailableStocks,@StockState,@Sum,@Pid)");
            //防sql注入 怕信息被盗
            SqlParameter[] param = new SqlParameter[]{
              //new SqlParameter("@StockID",SqlDbType.Int),
               new SqlParameter("@SpotStocks",s.SpotStocks),
                new SqlParameter("@AvailableStocks",s.AvailableStocks),
                 new SqlParameter("@StockState",s.StockState),
                  new SqlParameter("@Sum",s.Sum),
                  new SqlParameter("@Pid",s.Pid)
          };

            object rr = sql.ExecNonquery(str, param);

            return int.Parse(rr.ToString());
        }
    }
}
